A database system may be described as a computerized record keeping system whose overall purpose is to maintain information and to make that information available on demand. Almost all databases in use today are based on a "relational model" in which the database data is perceived by its users as a collection of tables. The tables in a relational database include a row of column names specifying one or more column fields, and zero or more data rows containing one scalar value for each of the column fields. Each column in a database stores data regarding a particular concept or object. Therefore, the value contained in each field in a column represents a separate instance of that concept. For example, a table pertaining to parts in a manufacturing database may contain a column representing the concept of part numbers, and each field in that column may contain a value representing one instance of part numbers.
One feature that distinguishes relational from nonrelational databases is the ability to "join" two or more tables. In general, a join is described as a query in which data is retrieved from the fields of more than one table (although data may also be retrieved by joining a table with itself). However, in some database environments, it is possible that column fields representing the same real-world concepts (e.g. cities or parts) in two different tables may not use the same field values to represent the same instances of those objects. There are several reasons why this is so. One reason has to do with the fact that table columns can support different data types, such as character strings, numeric data, and dates. Thus, even where two columns in different tables both support character strings to represent the same concepts or objects, the values used to fill those character strings may be different. For example, assume two columns represent types of transportation, such as automobiles, airplanes, trains etc. A field value representing the concept of automobile in one table may be the string "car", while a field value for automobile in another column may be the string "auto".
Another reason why column fields representing the same concepts in two different tables may have different field values is that the two columns have been designed to support different data types. For example one column representing types of transportation may do so with field values that are character strings, as described above, while another column represents the types of transportation with numeric codes, such as the code "50" to represent automobiles, the code "60" to represent airplanes, code "70" to represent trains, and so. In either of the examples above, if an attempt is made to perform a join across fields representing instances of the same concept but that use different field values, the join operation will fail because the sets of field values between the two columns do not equate. That is, although the field values "car", "auto", and the code "50" have the same meaning (automobile), the actual field values "car", "auto" and "50" do not equate and a join will fail.
Accordingly, what is needed is a method and system for performing joins across two or more fields that represent similar concepts using different field values. The present invention addresses such a need.